import pymysql
import json

TYPE_NEW = 0
TYPE_UPDATED = 1
TYPE_REMOVED = 2

SQL_QUERY_ALL = 'SELECT * FROM positions'
SQL_INSERT_POSITION = 'INSERT INTO positions (`ubr_id`,`date`,`title`,`dept_name`,`dept_code`,`desc_en`,`desc_zh`) VALUES(%s,%s,%s,%s,%s,%s,%s)'
SQL_DELETE_POSITION = 'DELETE FROM positions WHERE `ubr_id`=%s'
SQL_INSERT_POSITION_LOG = 'INSERT INTO position_logs (`ubr_id`,`type`,`payload`,`desc_en`,`desc_zh`) VALUES(%s,%s,%d,%s,%s,%s)'

with open('config.json', 'r') as config_file:
  _config = json.loads(config_file.read())

def get_conn():
  return pymysql.connect(_config['database']['host'], _config['database']['username'], _config['database']['password'], _config['database']['name'], charset = 'utf8')
  # return pymysql.connect('localhost', 'root', '', 'ubr_watch', charset = 'utf8')

def fetch_all_positions(db = None):
  if db == None:
    db = get_conn()
  c = db.cursor(cursor=pymysql.cursors.DictCursor)
  c.execute(SQL_QUERY_ALL)
  rows = c.fetchall()
  # print(rows)
  c.close()
  db.close()
  return rows
